package cn.doitedu.visualiz.platform;

import cn.doitedu.visualiz.platform.service.FunnelAnalysisServiceImpl;
import cn.doitedu.visualiz.platform.vo.FunnelParams;
import cn.doitedu.visualiz.platform.vo.FunnelStep;
import cn.doitedu.visualiz.platform.vo.PropertyParam;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;

import java.util.ArrayList;

/**
 with tmp as(
 SELECT
 guid,
 concat_ws(',',array_agg(concat_ws(':',cast(ts as varchar),eventid))) as elst

 FROM hive.test.action_dtl_ld
 WHERE
 (eventid='e1' and element_at(properties,'p1')='v1')
 or
 (eventid='e3' and element_at(properties,'p2')='v2')
 or
 (eventid='e5' and element_at(properties,'pt')='vr')

 GROUP BY guid
 )
 select
 guid,
 case
 when regexp_extract(elst,'.*?(e1).*?(e3).*?(e5).*?',3)='e5' then 3
 when regexp_extract(elst,'.*?(e1).*?(e3).*?',2)='e3' then 2
 when regexp_extract(elst,'.*?(e1).*?',1)='e1' then 1
 else 0
 end as step
 from tmp
 * ;
 */
public class DemoSql {

    public static void sql(FunnelParams funnelParams){

        String with = "with tmp as( SELECT guid,concat_ws(',',array_agg(concat_ws(':',cast(ts as varchar),eventid))) as elst  FROM hive.test.action_dtl_ld WHERE ";

        ArrayList<String> whereList = new ArrayList<>();
        for (FunnelStep step : funnelParams.getSteps()) {
            PropertyParam p1 = step.getPropertyParams().get(0);
            String where = String.format("(eventid='%1$s' and element_at(properties,'%2$s')='%3$s')",step.getEventId(), p1.getPropertyName(),p1.getCompareValue());
            whereList.add(where);
        }

        String tmpwhere = StringUtils.join(whereList, " or ");

        String groupby = "GROUP BY guid )";


        String select = "select guid, case ";

        /**
         *  when regexp_extract(elst,'.*?(e1).*?(e3).*?(e5).*?',3)='e5' then 3
         *  when regexp_extract(elst,'.*?(e1).*?(e3).*?',2)='e3' then 2
         *  when regexp_extract(elst,'.*?(e1).*?',1)='e1' then 1
         */

        StringBuilder when = new StringBuilder();
        for (int i=0;i<funnelParams.getSteps().size();i++) {
            StringBuilder sb2 = new StringBuilder();
            sb2.append("when regexp_extract(elst,'.*?");
            for(int j=0;j<funnelParams.getSteps().size()-i;j++) {
                String eid = funnelParams.getSteps().get(j).getEventId();
                sb2.append(String.format("(%1$s).*?",eid));
            }
            sb2.append(String.format("',%1$s)='%2$s' then %1$s ",funnelParams.getSteps().size()-i,funnelParams.getSteps().get(funnelParams.getSteps().size()-i-1).getEventId()));
            when.append(sb2);
        }
        String from = " else 0 end as step from tmp ";

        StringBuilder sql = new StringBuilder();
        sql.append(with).append(tmpwhere).append(groupby).append(select).append(when).append(from);

        System.out.println(sql.toString());


    }


    public static void main(String[] args) {

        FunnelParams funnelParams = new FunnelParams();
        funnelParams.setFunnelName("xxx");
        funnelParams.setTimeWindow(7);


        ArrayList<FunnelStep> steps = new ArrayList<>();

        FunnelStep step1 = new FunnelStep();
        step1.setEventId("e1");
        ArrayList<PropertyParam> propertyParams = new ArrayList<>();
        PropertyParam propertyParam = new PropertyParam();
        propertyParam.setPropertyName("p1");
        propertyParam.setCompareOperator("=");
        propertyParam.setCompareValue("v1");
        propertyParams.add(propertyParam);
        step1.setPropertyParams(propertyParams);


        FunnelStep step2 = new FunnelStep();
        step2.setEventId("e3");
        ArrayList<PropertyParam> propertyParams2 = new ArrayList<>();
        PropertyParam propertyParam2 = new PropertyParam();
        propertyParam2.setPropertyName("p3");
        propertyParam2.setCompareOperator("=");
        propertyParam2.setCompareValue("v3");
        propertyParams2.add(propertyParam2);
        step2.setPropertyParams(propertyParams2);


        steps.add(step1);
        steps.add(step2);

        funnelParams.setSteps(steps);

        sql(funnelParams);




    }

}
